<?php
/*
 * Deprecated: mysql_connect(): use mysqli or PDO instead
 */

header("Content-type: text/html; charset=utf-8");

// mySql
$mysql_server_name = "localhost";    //数据库服务器名称
$mysql_username = "root";             // 连接数据库用户名
$mysql_password = "";                // 连接数据库密码
$mysql_database = "my_db";           // 数据库名称

$con = mysql_connect($mysql_server_name, $mysql_username, $mysql_password);
if (!$con) {
    echo 'connect fail';
    die('Could not connect: ' . mysql_error());
}
echo 'connect success', '<br>';

// CREATE DATABASE
if (mysql_query("CREATE DATABASE IF NOT EXISTS $mysql_database", $con)) {
    echo "Database created" .'<br>';
} else {
    echo "Error creating database: " . mysql_error() . '<br>';
}

// Create table in my_db database
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE IF NOT EXISTS Persons
(
personID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)";


if (mysql_query($sql, $con)) {
    echo "TABLE created", '<br>';
}

// INSERT INTO
IF (mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin', '40')")) {
    echo "INSERT Peter", '<br>';
}

if (mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire', '33')")) {
    echo "INSERT Glenn", '<br>';
}

if (mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('高峰', 'Quag地方vdsmire', '21')")) {
    echo "INSERT Glenn", '<br>';
}

/*
 * SELECT
 */
// WHERE
// $result = mysql_query("SELECT * FROM Persons WHERE FirstName='Peter'");
// ORDER BY
// $result = mysql_query("SELECT * FROM Persons ORDER BY age");
// 默认是升序, DESC降序排序. 可以根据多个列进行排序。当按照多个列进行排序时，只有第一列相同时才使用第二列：
$result = mysql_query("SELECT * FROM Persons ORDER BY  personID, age DESC");

echo 'SELECT RESULT:' . '<br>';
while ($row = mysql_fetch_array($result)) {
    echo $row['personID'] . " " . $row['FirstName'] . " " . $row['LastName'] . " " . $row['Age'] . "<br />";
}


// UPDATE
mysql_query("UPDATE Persons SET Age = '36'
WHERE FirstName = 'Peter' AND LastName = 'Griffin'");

// DELETE
mysql_query("DELETE FROM Persons  WHERE FirstName='Glenn'");
// mysql_query("DELETE FROM Persons");

// close
echo 'close connect', '<br>';
mysql_close($con);